package database;
// File: BranchModel.java

import gui.ExceptionEvent;

import java.sql.*;


/*
 * BranchModel is a database interface class that provides methods to 
 * insert a branch, update the name of a branch, delete a branch, return 
 * a ResultSet containing all branches, determine the existence of a particular 
 * branch, and return the database connection used by this class. 
 *
 * Each insert, update, and delete method is treated as a transaction, i.e., they 
 * commit the change. If you want to use those methods as helper functions for a 
 * larger transaction, you would have to write non commit() versions of those methods, 
 * so that you can treat the larger transaction as a single transaction. 
 * 
 * The database connection in MvbOracleConnection must be set before any database
 * access methods can be used. (This was done when connect() was called by the 
 * LoginWindow). This class is defined in MvbOracleConnection.java. 
 *
 * BranchModel allows components to register for exception events when 
 * an exception occurs in the model.  Exceptions are part of the 
 * Java 2 API, but exception events aren't.  We are creating our own 
 * event/listener. The BranchController class registers itself as
 * a listener to BranchModel so that it can display the exception 
 * messages.  The file ExceptionEvent.java contains the class 
 * definition of ExceptionEvent.  The file ExceptionListener.java
 * contains the interface that components must implement in order 
 * to receive exception events.   
 */
public class ManagerData extends DataSuperclass {
	/*
	 * Default constructor Precondition: The Connection object in
	 * MvbOracleConnection must be a valid database connection.
	 */
	public ManagerData() {
		con = OracleConnection.getInstance().getConnection();
	}

	/*
	 * Inserts a tuple into the branch table. The object wrapper for the int
	 * datatype is used so that null can be inserted, e.g., to insert null into
	 * the branch_phone column, set the bphone argument to null. If a 10 digit
	 * phone number is used, change the data type for the phone number from
	 * Integer to Long. Returns true if the insert is successful; false
	 * otherwise.
	 * 
	 * Only baddr and bphone can be null.
	 */
	public boolean insertItem(Integer upc, String title, String type, String category, String company, Integer year, Double iprice,	Integer stock) {
		try {
			ps = con.prepareStatement("INSERT INTO item VALUES (?,?,?,?,?,?,?,?)");

			ps.setInt(1, upc);
			ps.setString(2, title);
			ps.setString(3, type);
			ps.setString(4, category);
			ps.setString(5, company);
			ps.setInt(6, year);
			ps.setDouble(7, iprice);
			ps.setInt(8, stock);

			ps.executeUpdate();

			con.commit();

			return true;
		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);

			try {
				con.rollback();
				return false;
			} catch (SQLException ex2) {
				event = new ExceptionEvent(this, ex2.getMessage());
				fireExceptionGenerated(event);
				return false;
			}
		}
	}

	/*
	 * Updates the name of a branch Returns true if the update is successful;
	 * false otherwise.
	 * 
	 * bname cannot be null.
	 */
	public boolean updateBranch(int bid, String bname) {
		try {
			ps = con.prepareStatement("UPDATE branch SET branch_name = ? WHERE branch_id = ?");

			if (bname != null) {
				ps.setString(1, bname);
			} else {
				return false;
			}

			ps.setInt(2, bid);

			ps.executeUpdate();

			con.commit();

			return true;
		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);

			try {
				con.rollback();
				return false;
			} catch (SQLException ex2) {
				event = new ExceptionEvent(this, ex2.getMessage());
				fireExceptionGenerated(event);
				return false;
			}
		}
	}

	/*
	 * Deletes a branch. Returns true if the delete is successful; false
	 * otherwise.
	 */
	public boolean deleteBranch(int bid) {
		try {
			ps = con.prepareStatement("DELETE FROM branch WHERE branch_id = ?");

			ps.setInt(1, bid);

			ps.executeUpdate();

			con.commit();

			return true;
		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);

			try {
				con.rollback();
				return false;
			} catch (SQLException ex2) {
				event = new ExceptionEvent(this, ex2.getMessage());
				fireExceptionGenerated(event);
				return false;
			}
		}
	}

	/*
	 * Returns a ResultSet containing all branches. The ResultSet is scroll
	 * insensitive and read only. If there is an error, null is returned.
	 */
	public ResultSet showBranch() {
		try {
			ps = con.prepareStatement("SELECT * FROM item",
					// ps = con.prepareStatement("SELECT b.* FROM branch b",

					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);

			ResultSet rs = ps.executeQuery();

			return rs;
		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);
			// no need to commit or rollback since it is only a query

			return null;
		}
	}

	/*
	 * Same as showBranch() except that an updatable result set is returned.
	 */
	public ResultSet editBranch() {
		try {
			// ps = con.prepareStatement("SELECT b.* FROM branch b",
			ps = con.prepareStatement("SELECT i.* FROM item i",
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_UPDATABLE);

			ResultSet rs = ps.executeQuery();

			return rs;
		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);
			// no need to commit or rollback since it is only a query

			return null;
		}
	}

	/*
	 * Returns true if the branch exists; false otherwise.
	 */
	public boolean findItem(int upc) {
		try {
			ps = con.prepareStatement("SELECT upc FROM item where upc = ?");

			ps.setInt(1, upc);

			ResultSet rs = ps.executeQuery();

			if (rs.next()) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);

			return false;
		}
	}

	public boolean adjustStock(int upc, int delta) {
		try {
			ps = con.prepareStatement("SELECT stock FROM item where upc = ?");

			ps.setInt(1, upc);

			ResultSet rs = ps.executeQuery();
			rs.next();
			
			int stock = rs.getInt(1) + delta;

			ps = con.prepareStatement("UPDATE item SET stock = ? WHERE upc = ?");

			ps.setInt(1, stock);
			ps.setInt(2, upc);

			ps.executeQuery();

			return true;
		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);

			return false;
		}
	}
	
	public boolean adjustPrice(int upc, double price) {
		try {

			ps = con.prepareStatement("UPDATE item SET price = ? WHERE upc = ?");

			ps.setDouble(1, price);
			ps.setInt(2, upc);

			ps.executeQuery();

			return true;
		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);

			return false;
		}
	}

	
	public ResultSet salesReport(Date date) {
		


		
		try {
			ps = con.prepareStatement(
"SELECT " +
"i.upc, i.category, i.price as Unit_Price, sum(pi.purchase_qty) as units, sum(pi.purchase_qty)*i.price as unit_total " +
"FROM purchase_item pi " +
"join item i on i.upc=pi.upc " +
"join purchase p on p.receipt_id = pi.receipt_id " +
"WHERE purchase_date = ? " +
"group by i.upc, i.category, i.price",					

					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);

			ps.setDate(1, date);
			ResultSet rs = ps.executeQuery();

			return rs;
		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);
			// no need to commit or rollback since it is only a query

			return null;
		}
		
	}

	public ResultSet bestSellers(Date ldate, int lnum) {
	
		try {
			ps = con.prepareStatement(
"select sum(pi.purchase_qty) as units_sold, i.upc, i.title, i.company, i.stock FROM purchase_item pi join item i on i.upc=pi.upc join purchase p on p.receipt_id = pi.receipt_id WHERE purchase_date = ? group by i.upc, i.title, i.company, i.stock ",					

					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		try {
			ps.setDate(1, ldate);
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		ResultSet rs = null;
		try {
			rs = ps.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return rs;
		
}

}
